ALTER FUNCTION [dbo].[CalInterestWkly]
(
	@report_date DATETIME,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

	DECLARE @interest DECIMAL(18, 8) = 0,
		@wkly_interest DECIMAL(18, 8) = 0,	
		@payment_date DATETIME = DATEADD(wk, 1, @loan_start_date),
		@loan_end_date DATETIME = DATEADD(week, @term, @loan_start_date),
		@num_days INTEGER,	
		@month_end DATETIME, 
		@counter INTEGER = 1
	
	
	--IF @loan_start_date < @report_date AND (@loan_end_date > @report_date OR (YEAR(@loan_end_date) = YEAR(@report_date) AND MONTH(@loan_end_date) = MONTH(@report_date)))
	--BEGIN
	--	SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @loan_start_date), -1)) - DAY(@loan_start_date), @loan_start_date)
		
	--	IF YEAR(@loan_start_date) = YEAR(@report_date) AND MONTH(@loan_start_date) = MONTH(@report_date)
	--	BEGIN
	--		SET @num_days = DATEDIFF(day, @loan_start_date, @report_date)			
	--	END
	--	ELSE
	--	BEGIN
	--		SET @num_days = DATEDIFF(day, @loan_start_date, @month_end)	
	--	END		
		 
				
	--	SET @interest = @principal * @daily_interest_rate * @num_days
	--	SET @principal = @principal + @interest - @payment_amount 
	--	SET @month_end = DATEADD(month, 1, @month_end)
		
		
	--	WHILE (YEAR(@month_end) < YEAR(@report_date) OR (YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) < MONTH(@report_date)))
	--	BEGIN
	--		SET @interest = @principal * @mnthly_interest_rate			
	--		SET @principal = @principal + @interest - @payment_amount
	--		SET @month_end = DATEADD(month, 1, @month_end)
	--	END	
		
	--	IF YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) = MONTH(@report_date)
	--	BEGIN
	--		IF @report_date <= @loan_end_date
	--		BEGIN
	--			SET @num_days = DAY(@report_date)
	--		END
	--		ELSE
	--		BEGIN
	--			SET @num_days = DAY(@loan_end_date)
	--		END
			
	--		SET @interest = @principal * @daily_interest_rate * @num_days
	--	END	
	--END
	--RETURN @interest
	
	

	SET @interest_rate = (@interest_rate / 100) / 52

	IF @payment_date < @report_date
	BEGIN
		WHILE (YEAR(@payment_date) < YEAR(@report_date) OR (YEAR(@payment_date) = YEAR(@report_date) AND MONTH(@payment_date) < MONTH(@report_date)))
		BEGIN
			SET @wkly_interest = @principal * @interest_rate
			SET @interest = @interest + @wkly_interest
			SET @principal = @principal + @wkly_interest - @payment_amount
			SET @payment_date = DATEADD(wk, 1, @payment_date)
			SET @counter = @counter + 1
		END	

		SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @payment_date), -1)) - DAY(@payment_date), @payment_date)
		SET @interest = 0

		WHILE @payment_date <= @report_date
		BEGIN
			IF @counter <= @term
			BEGIN
				SET @wkly_interest = @principal * @interest_rate
				SET @interest = @interest + @wkly_interest
				SET @principal = @principal + @wkly_interest - @payment_amount
				
				SET @payment_date = DATEADD(wk, 1, @payment_date)
				IF @payment_date > @report_date
				BEGIN
					SET @wkly_interest = ((@interest_rate * 52) / 365 ) * @principal * DATEDIFF(day, @report_date, @payment_date)
					SET @interest = @interest + @wkly_interest
				END
				SET @counter = @counter + 1
			END	
			ELSE
			BEGIN
				BREAK
			END	
		END
	END
	RETURN @interest
END;

